import sqlite3
from typing import List, Dict, Any, Optional

class SQLiteDataTool:
    """SQLite数据库操作工具类"""
    
    def __init__(self, db_path: str):
        """
        初始化SQLite数据库连接
        
        Args:
            db_path (str): SQLite数据库文件路径
        """
        self.db_path = db_path
        
    def create_connection(self) -> sqlite3.Connection:
        """创建数据库连接"""
        return sqlite3.connect(self.db_path)
        
    def get_table_names(self) -> List[str]:
        """获取数据库中所有表的名称"""
        conn = self.create_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
            return [row[0] for row in cursor.fetchall()]
        finally:
            conn.close()
            
    def get_table_info(self, table_name: str) -> List[Dict[str, Any]]:
        """
        获取表的结构信息
        
        Args:
            table_name (str): 表名
            
        Returns:
            List[Dict[str, Any]]: 包含列名、类型等信息的列表
        """
        conn = self.create_connection()
        try:
            cursor = conn.cursor()
            cursor.execute(f"PRAGMA table_info({table_name})")
            columns = cursor.fetchall()
            return [{
                'name': col[1],
                'type': col[2],
                'notnull': bool(col[3]),
                'default': col[4],
                'primary_key': bool(col[5])
            } for col in columns]
        finally:
            conn.close()
            
    def create_table(self, table_name: str, columns: Dict[str, str], 
                    primary_key: Optional[str] = None) -> bool:
        """
        创建新表
        
        Args:
            table_name (str): 表名
            columns (Dict[str, str]): 列名和类型映射
            primary_key (Optional[str]): 主键列名
            
        Returns:
            bool: 是否成功创建
        """
        conn = self.create_connection()
        try:
            cursor = conn.cursor()
            columns_str = ', '.join([
                f"{name} {col_type} {'PRIMARY KEY' if name == primary_key else ''}" 
                for name, col_type in columns.items()
            ])
            cursor.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_str})")
            conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"Error creating table: {str(e)}")
            return False
        finally:
            conn.close()
            
    def insert_data(self, table_name: str, data: Dict[str, Any]) -> bool:
        """
        插入单行数据
        
        Args:
            table_name (str): 表名
            data (Dict[str, Any]): 要插入的数据字典
            
        Returns:
            bool: 是否成功插入
        """
        conn = self.create_connection()
        try:
            cursor = conn.cursor()
            columns = ', '.join(data.keys())
            placeholders = ', '.join(['?'] * len(data))
            values = tuple(data.values())

            cursor.execute(f"PRAGMA table_info({table_name})")
            cursor.execute(f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})", values)
            conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"Error inserting data: {str(e)}")
            print(e.sqlite_errorcode)
            return False
        finally:
            conn.close()
            
    def update_data(self, table_name: str, data: Dict[str, Any], 
                    where_clause: str, params: tuple) -> bool:
        """
        更新数据
        
        Args:
            table_name (str): 表名
            data (Dict[str, Any]): 要更新的数据字典
            where_clause (str): WHERE子句
            params (tuple): WHERE子句的参数
            
        Returns:
            bool: 是否成功更新
        """
        conn = self.create_connection()
        try:
            cursor = conn.cursor()
            set_clause = ', '.join([f"{col}=?" for col in data.keys()])
            values = tuple(data.values()) + params
            
            cursor.execute(f"UPDATE {table_name} SET {set_clause} WHERE {where_clause}", values)
            conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"Error updating data: {str(e)}")
            return False
        finally:
            conn.close()
            
    def delete_data(self, table_name: str, where_clause: str, params: tuple) -> bool:
        """
        删除数据
        
        Args:
            table_name (str): 表名
            where_clause (str): WHERE子句
            params (tuple): WHERE子句的参数
            
        Returns:
            bool: 是否成功删除
        """
        conn = self.create_connection()
        try:
            cursor = conn.cursor()
            cursor.execute(f"DELETE FROM {table_name} WHERE {where_clause}", params)
            conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"Error deleting data: {str(e)}")
            return False
        finally:
            conn.close()
            
    def query_data(self, table_name: str, columns: List[str] = None, 
                  where_clause: str = None, params: tuple = None, 
                  limit: int = None) -> []:
        """
        查询数据
        
        Args:
            table_name (str): 表名
            columns (List[str]): 要查询的列名列表
            where_clause (str): WHERE子句
            params (tuple): WHERE子句的参数
            limit (int): 返回结果的数量限制
            
        Returns:
            pd.DataFrame: 查询结果
        """
        conn = self.create_connection()
        try:
            cursor = conn.cursor()
            
            # 构建查询语句
            select_cols = '*'
            if columns:
                select_cols = ', '.join(columns)
                
            query = f"SELECT {select_cols} FROM {table_name}"
            
            # 添加WHERE子句
            if where_clause:
                query += f" WHERE {where_clause}"
                
            # 添加LIMIT
            if limit:
                query += f" LIMIT {limit}"
                
            # 执行查询
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
                
            # 获取列名

            # 获取结果
            return cursor.fetchall()
            
            # 返回结果

        finally:
            conn.close()
